package com.pineapple.module.core.remove.service;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Locale;
import java.util.Map;

import com.pineapple.module.core.remove.domain.Remove;
import com.pineapple.module.core.remove.domain.RemoveCriteria;
import com.pineapple.module.core.remove.domain.RemoveSearch;

public class RemoveDAO {
	protected int count(RemoveCriteria criteria,Connection conn) throws Exception {
		int totRecord =0;

		StringBuilder sql = new StringBuilder();
		sql.append("SELECT COUNT(*) AS TOT FROM remove ");
		sql.append("WHERE 1=1 ");
		if(criteria.getRemoveId() !=null && !criteria.getRemoveId().equals("")){
			sql.append("AND remove_id='"+criteria.getRemoveId()+"' ");
		}
		if(criteria.getRemoveDateFrom() !=null && !criteria.getRemoveDateFrom().equals("")){
			sql.append("AND remove_date >='"+dateForInsert(criteria.getRemoveDateFrom())+"' ");
		}
		if(criteria.getRemoveDateTo() !=null && !criteria.getRemoveDateTo().equals("")){
			sql.append("AND remove_date <='"+dateForInsert(criteria.getRemoveDateTo())+"' ");
		}
		System.out.println(sql.toString());
		try(Statement stmt = conn.createStatement()){
			ResultSet rst = stmt.executeQuery(sql.toString());
			if (rst.next()) {
				totRecord = rst.getInt("TOT");
			}
		}catch(Exception ex){
			ex.printStackTrace();
			throw ex;
		}
		return totRecord;
	}



	protected List<RemoveSearch> search(RemoveCriteria criteria,Connection conn) throws Exception{
		List<RemoveSearch> lstRemove = new ArrayList<RemoveSearch>();
		RemoveSearch remove = null;
		StringBuilder sql = new StringBuilder();
		sql.append("SELECT a.area_name,r.remove_date,r.remove_id ");
		sql.append("FROM ");
		sql.append("remove r INNER JOIN planting p ON r.planting_id = p.planting_id ");
		sql.append("INNER JOIN area a ON p.area_id = a.area_id " );
		sql.append("WHERE 1=1 ");

		if(criteria.getRemoveId() !=null && !criteria.getRemoveId().equals("")){
			sql.append("AND remove_id='"+criteria.getRemoveId()+"' ");
		}
		if(criteria.getRemoveDateFrom() !=null && !criteria.getRemoveDateFrom().equals("")){
			sql.append("AND remove_date >='"+dateForInsert(criteria.getRemoveDateFrom())+"' ");
		}
		if(criteria.getRemoveDateTo() !=null && !criteria.getRemoveDateTo().equals("")){
			sql.append("AND remove_date <='"+dateForInsert(criteria.getRemoveDateTo())+"' ");
		}
		sql.append("LIMIT "+(criteria.getStart()-1)+","+((criteria.getStart()-1) + criteria.getLinePerPage()));
		System.out.println(sql.toString());
		try(Statement stmt = conn.createStatement()){
			  ResultSet rs = stmt.executeQuery(sql.toString());
		        while(rs.next()) {
		        	remove = new RemoveSearch();
		        	remove.setRemoveId(rs.getString("remove_id"));
		        	remove.setAreaName(rs.getString("area_name"));
		        	remove.setRemoveDate(dateForShow(rs.getDate("remove_date")));
		        	lstRemove.add(remove);
		        }

		}catch(Exception ex){
			ex.printStackTrace();
		}
		return lstRemove;
	}


	protected void add(Connection conn, Remove remove)throws Exception{
		StringBuilder sql = new StringBuilder();
		sql.append("INSERT INTO remove (planting_id,remove_date)");
		sql.append("VALUES (");
		sql.append("'"+remove.getPlantingId()+"' ");
		sql.append(",'"+dateForInsert(remove.getRemoveDate())+"' )");
		System.out.println(sql.toString());
		try(Statement stmt = conn.createStatement()){
			 stmt.executeUpdate(sql.toString());

		}catch(Exception ex){
			ex.printStackTrace();
			throw ex;
		}

	}


	protected void edit(Connection conn, Remove remove)throws Exception{
		StringBuilder sql = new StringBuilder();
		sql.append("UPDATE remove SET ");
		sql.append("planting_id='"+remove.getPlantingId()+"' ");
		sql.append("remove_date='"+remove.getRemoveDate()+"' ");
		sql.append(" WHERE remove_id="+remove.getRemoveId());

		System.out.println(sql.toString());
		try(Statement stmt = conn.createStatement()){
			stmt.executeUpdate(sql.toString());

		}catch(Exception ex){
			ex.printStackTrace();
			throw ex;
		}
	}

	protected void delete(Connection conn, String removeId)throws Exception{
		StringBuilder sql = new StringBuilder();
		sql.append("DELETE FROM remove  ");

		sql.append(" WHERE remove_id IN ("+removeId+")");
		System.out.println(sql.toString());
		try(Statement stmt = conn.createStatement()){
			stmt.executeUpdate(sql.toString());
		}catch(Exception ex){
			ex.printStackTrace();
			throw ex;
		}

	}


	protected Remove searchById(Connection conn,int id)throws Exception{
		StringBuilder sql = new StringBuilder();
		sql.append("SELECT * FROM remove ");
		sql.append("WHERE remove_id ="+id);
		Remove remove= null;
		try(Statement stmt = conn.createStatement()){
			  ResultSet rs = stmt.executeQuery(sql.toString());
			  remove = new Remove();
		        if(rs.next()) {
		        	remove.setRemoveId(rs.getString("remove_id"));
		        	remove.setPlantingId(rs.getString("planting_id"));
		        	remove.setRemoveDate(dateForShow(rs.getDate("remove_date")));
		        }
		}catch(Exception ex){
			ex.printStackTrace();
			throw ex;
		}
		return remove;

	}

	protected Map<String,String> searchComboPlantAndArea(Connection conn) throws Exception{
		StringBuilder sql = new StringBuilder();
		Map<String,String> lstArea = new LinkedHashMap<String, String>();
		sql.append("SELECT a.area_name,p.planting_id,a.area_id ");
		sql.append("FROM planting p INNER JOIN area a ON p.area_id = a.area_id ");
		sql.append("WHERE p.planting_status=11 ");

		try(Statement stmt = conn.createStatement()){
			  ResultSet rs = stmt.executeQuery(sql.toString());

		        while(rs.next()) {
		        	lstArea.put(rs.getString("planting_id"), rs.getString("area_name"));
		        }
		}catch(Exception ex){
			ex.printStackTrace();
			throw ex;
		}
		return lstArea;
	}



	public static String dateForShow(Date date){
		String returnValue = null;
		try {
			if(date != null){

				returnValue = convertToString(date,"dd/MM/yyyy");
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return returnValue;
	}

	public static String convertToString(Date date, String format) {
		if (date != null) {
			SimpleDateFormat formatDate = new SimpleDateFormat(format.toString(),Locale.ENGLISH);

			StringBuilder fdate = new StringBuilder(formatDate.format(date));
			return fdate.toString();
		} else {
			return "";
		}
	}


	public static String dateForInsert(String date){
		String returnValue = null;
		SimpleDateFormat sdf = new SimpleDateFormat("dd/MM/yyyy", new Locale("en", "EN"));
		try {
			if(date != null && !date.trim().equals("")){
				returnValue = convertToString(sdf.parse(date),"yyyy-MM-dd");
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return returnValue;
	}

}
